{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "from sqlalchemy import create_engine, MetaData\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import math\n",
    "from scipy import stats\n",
    "\n",
    "from shapely.geometry import Polygon\n",
    "from shapely.ops import transform\n",
    "import pyproj\n",
    "\n",
    "#from geoalchemy2 import Geometry  # <= not used but must be imported"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "api_engine = create_engine(f\"postgres://marxan-api:marxan-api@marxan-postgresql-api:5432/marxan-api\")\n",
    "api_meta = MetaData(schema=\"public\")\n",
    "api_meta.reflect(bind=api_engine)\n",
    "api_meta.tables.keys()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "dict_keys(['public.spatial_ref_sys', 'public.migrations', 'public.users_organizations', 'public.organizations', 'public.users', 'public.roles', 'public.users_projects', 'public.projects', 'public.issued_authn_tokens', 'public.features', 'public.output_results', 'public.scenarios', 'public.users_scenarios', 'public.api_event_kinds', 'public.api_events'])"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "geo_api_engine = create_engine(f\"postgres://marxan-geo-api:marxan-geo-api@marxan-postgresql-geo-api:5432/marxan-geo-api\")\n",
    "geo_api_meta = MetaData(schema=\"public\")\n",
    "geo_api_meta.reflect(bind=geo_api_engine)\n",
    "geo_api_meta.tables.keys()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "dict_keys(['public.spatial_ref_sys', 'public.migrations', 'public.admin_regions', 'public.admin_regions_0', 'public.admin_regions_1', 'public.admin_regions_2', 'public.wdpa', 'public.features_data', 'public.planning_units_geom', 'public.planning_units_geom_square', 'public.planning_units_geom_hexagon', 'public.planning_units_geom_irregular', 'public.scenarios_pu_data', 'public.scenarios_pu_cost_data', 'public.output_results_data', 'public.scenario_features_data'])"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "From the api DB we will need all info related the project and scenario a user has created/updated;\n",
    "``` sql\n",
    "select * from scenarios s \n",
    "right join projects p  on s.project_id = p.id \n",
    "where p.id = '2a800cc9-b436-4c3d-b781-54b024e3adbb'\n",
    "```\n",
    "For the geoprocessing in order to be able to create the pu linked to that scenario:\n",
    "\n",
    "* We need the extent and typology of PU provided in the project data. \n",
    "* We need the scenario id in order to link it in the scenarios_pu_data.\n",
    "* We need the wdpa filtering info (ids and/or category filters) in order to select and intersect our pus.\n",
    "\n",
    "We need also to have in place mechanism of what will happen if update or delete at project/scenario happens and how is that going to be propagated.\n",
    "We will also need to alter pu data / project tables in order to accomodate user uploaded pu\n",
    "\n",
    "two steps query -> scenario pu association + wdpa calcs and initial lockin\n",
    "\n",
    "**@todo**: we need to take into account area calculation variates due projection, also reprojection operations can be costly in Postgres. Andrew uses [EPSG:3410](https://epsg.io/3410) for area calculation literature seems to [be adecuate](https://nsidc.org/ease/ease-grid-projection-gt) (waht will happen in extensions above the recommended use of this projection), For tile displaying we will need the data in [EPSG:3857](https://epsg.io/3857), while for data integrity i would keep the projection in [EPSG:4326](https://epsg.io/4326). Also take into account geometry vs geography types in postgres in order to calculate areas. \n",
    "\n",
    "\n",
    "Scenario pu association:\n",
    "``` sql\n",
    "INSERT INTO scenarios_pu_data (pu_geom_id, scenario_id, puid)    \n",
    "select id as pu_geom_id, '2a800cc9-b436-4c3d-b781-54b024e3adbb' as scenario_id, row_number() over () as puid\n",
    "from planning_units_geom pug \n",
    "-- this where conditions are based on project pu creation optionss\n",
    "where type='square' and size = 100 and st_intersects(the_geom,ST_GeomFromText('MULTIPOLYGON (((-10 -10, 10 -10, 10 10, -10 -10)))',4326));\n",
    "```\n",
    "\n",
    "Wdpa calcs and initial lockin base on threshold. A pu can intersect with more than one pa so in order to properly calculate this narrow case we need to aggregate by id.\n",
    "\n",
    "``` sql\n",
    "with pa as (select * from wdpa where iucn_cat in ('Ia')), --or id in (),\n",
    "pu as (\n",
    "select spd.id, pug.the_geom\n",
    "from scenarios_pu_data spd  \n",
    "inner join planning_units_geom pug on spd.pu_geom_id = pug.id\n",
    "where scenario_id='2a800cc9-b436-4c3d-b781-54b024e3adbb'),\n",
    "pu_pa as (select pu.id, st_area(st_intersection(pu.the_geom, pa.the_geom)) as pa_pu_area, (CASE pu.the_geom && pa.the_geom WHEN true THEN 2 else 0 end) as lockin_status\n",
    "          from pu\n",
    "          left join pa on pu.the_geom && pa.the_geom) \n",
    "UPDATE scenarios_pu_data\n",
    "SET (protected_area, lockin_status) =\n",
    "    (SELECT protected_area, lockin_status FROM (select id, sum(pa_pu_area) as protected_area, max(lockin_status) as lockin_status \n",
    "                                                from pu_pa group by id) as result\n",
    "     WHERE scenarios_pu_data.id = result.id) where scenario_id = '2a800cc9-b436-4c3d-b781-54b024e3adbb';\n",
    "\n",
    "```\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Once all of this is done we will be able to generate the next requires files for marxan: \n",
    "* planningUnits.\n",
    "----\n",
    "- Job to link scenario and PU should be trigger at scenario creation and an scenario update.  \n",
    "- PA intersection Job should be trigger after Scenario-PU link creation or after scenario changes on wdpa filter options;\n",
    "- Clean job in cascade should be trigger after an scenario has been deleted.\n"
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}